using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectManyModule_Command(SqlGuid SessionGuid, SqlGuid FileGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string selectManyModuleCommandCommandText =
                @"
                    SELECT 
                        MC.CommandGuid, MC.CommandId, MC.FileGuid, MF.FileId, MF.FileOrder, MC.CommandName, MC.CommandCode, MC.CommandType 

                    FROM 
                        Module_Command MC

                    INNER JOIN 
                        Module_File MF
                        ON MF.FileGuid = MC.FileGuid

                    WHERE 
                        MC.FileGuid = @FileGuid

                    AND
                        CommandGuid IN 
                        (
                            SELECT
                                SCR.CommandGuid

                            FROM 
                                Security_Session SS

                            INNER JOIN
                                Security_UserRole SUR
                                ON SS.UserGuid = SUR.UserGuid

                            INNER JOIN
                                Security_CommandRole SCR
                                ON SUR.RoleGuid = SCR.RoleGuid

                            WHERE 
                                SessionGuid = @SessionGuid
                        )
                ";

                SqlCommand selectManyModuleCommandCommand = new SqlCommand(selectManyModuleCommandCommandText, connection);

                selectManyModuleCommandCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));
                selectManyModuleCommandCommand.Parameters.Add(new SqlParameter("@FileGuid", FileGuid));

                SqlContext.Pipe.ExecuteAndSend(selectManyModuleCommandCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
